﻿using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace IP.Core.IPBusinessService
{
	/// <summary>
	/// Summary description for CCommandBuilder.
	/// </summary>
	public class CCommandBuilder
	{
		//Tao cac cau lenh cap nhat du lieu ;
		//Dinh nghia cac hang so;
		private DataSet m_ds;
		private string m_TableName;
		private OracleConnection m_cnn;
		public CCommandBuilder(OracleConnection i_cnn, DataSet i_ds, string i_TableName)
		{
			//
			// TODO: Add constructor logic here
			//
			m_cnn=i_cnn;
			m_ds=i_ds;
			m_TableName=i_TableName;
		}
		/// <summary>
		/// tạo ra câu lệnh select
		/// </summary>
		/// <param name="i_sDieuKien"></param>
		/// <returns></returns>
		public OracleCommand getSelectCommand(string i_sDieuKien){
			OracleCommand v_Cmd;
			v_Cmd=new OracleCommand(getSelectString(i_sDieuKien),m_cnn);
			v_Cmd.CommandType=CommandType.Text;
			return v_Cmd;
		}
		//Tao cau lenh Select.
		public string getSelectString(string i_sDieuKien){
			string v_strSelect="Select ";
			foreach (DataColumn v_DataColumn in m_ds.Tables[m_TableName].Columns){
				v_strSelect+=v_DataColumn.ColumnName + ", ";
			}
			v_strSelect=v_strSelect.Substring(0, v_strSelect.Length -2);
			v_strSelect+=" from " + m_TableName;
			v_strSelect+=" " + i_sDieuKien;
			return v_strSelect;
		}
		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public OracleCommand getUpdateCommand(){
			OracleCommand v_Cmd=new OracleCommand();
			v_Cmd.Connection=m_cnn;
			v_Cmd.CommandType=CommandType.StoredProcedure;
			v_Cmd.CommandText= m_TableName + "_P." + "update_" + m_TableName ;
			setParaCollection4CmdForUD(v_Cmd);
			return v_Cmd;

		}
		/// <summary>
		/// kiểu client side command
		/// </summary>
		/// <returns></returns>
		public OracleCommand get_client_side_UpdateCommand(){
			OracleCommand v_cmd=new OracleCommand();
			v_cmd.Connection=m_cnn;
			v_cmd.CommandType=CommandType.Text;
			setParaCollection4CmdForUD(v_cmd);
			v_cmd.CommandText= get_client_side_update_command_text(v_cmd); // get command text;
			return v_cmd;	

		}
		/// <summary>
		/// Tạo command text ở phía client, và bind các tham số vào
		/// 
		/// </summary>
		public OracleCommand get_client_side_InserCommand(){
			OracleCommand v_cmd=new OracleCommand();
			v_cmd.Connection=m_cnn;
			v_cmd.CommandText= get_client_side_insert_command_text(); // get command text;
			v_cmd.CommandType=CommandType.Text;
			setParaCollection4CmdForInsert(v_cmd);
			return v_cmd;
		}
		/// <summary>
		/// command text for update
		/// 
		/// </summary>
		/// <returns></returns>
		protected string get_client_side_update_command_text(OracleCommand i_cmd){
			string v_str_command_text = "begin ";
			v_str_command_text += m_TableName + "_P." + "update_" + m_TableName ;
			v_str_command_text += "(  ";
			
			// các tham số khác
			DataRow v_Row;
			v_Row=m_ds.Tables[m_TableName].Rows[0];
			foreach (DataColumn v_Column in m_ds.Tables[m_TableName].Columns){
					string v_str_param_name = "ip_" + v_Column.ColumnName;
					v_str_command_text += 
										 v_str_param_name 
										  + " => "  
										  + OracleParameterConvert.get_string_value_for_DML(i_cmd.Parameters[v_str_param_name]) + ",";
			}
			v_str_command_text = v_str_command_text.Substring(0,v_str_command_text.Length-1);
			v_str_command_text += "); ";
			v_str_command_text += "  end;";
			return v_str_command_text;
		
		}


		//

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public OracleCommand getInsertCommand(){
			OracleCommand v_cmd=new OracleCommand();
			v_cmd.Connection=m_cnn;
			v_cmd.CommandText=m_TableName + "_P." + "insert_" + m_TableName ;
			v_cmd.CommandType=CommandType.StoredProcedure;
			setParaCollection4CmdForInsert(v_cmd);
			return v_cmd;
		}
		/// <summary>
		/// sử dụng trong ipbusiness service only
		/// </summary>
		/// <param name="i_str_table_name"></param>
		/// <returns></returns>
		static public string getDeleteByIDCommandText(string i_str_table_name){
			return i_str_table_name + "_P." + "delete_" + i_str_table_name ;
		}
		/// <summary>
		/// chi lock duoi csdl
		/// </summary>
		/// <returns></returns>
		
		public OracleCommand getUpdatableCommand()
		{
			OracleCommand v_cmd=new OracleCommand();
			v_cmd.Connection=m_cnn;
			v_cmd.CommandType=CommandType.Text;
			v_cmd.CommandText = "select * from " + m_TableName;
			decimal v_id = (decimal)m_ds.Tables[0].Rows[0]["ID"];
			v_cmd.CommandText += "  where ID = " +  v_id.ToString();
			v_cmd.CommandText += " for update nowait";	
			return v_cmd;
		}
		
		/// <summary>
		/// set giá trị cho tham số đầu vào
		/// <list type="bullet">
		/// <item>set giá trị </item>
		/// <item>điều chỉnh các giá trị null, thành DBNull</item>
		/// </list>
		/// </summary>
		/// <param name="i_oracle_para"></param>
		/// <param name="i_obj_property"></param>
		/// <param name="i_dr_value"></param>
		protected void set_value_4_para(OracleParameter i_oracle_para, 
										CProperty i_obj_property,
										DataRow i_dr_value){
			Type v_obj_type=i_dr_value.Table.Columns[i_obj_property.getName()].DataType;
			switch(v_obj_type.ToString()){
				case "System.String":
					if(i_dr_value.IsNull(i_obj_property.getName())){
						i_oracle_para.Value=System.Convert.DBNull;
					}
					else if(i_dr_value[i_obj_property.getName()].ToString().Trim().Length==0){
						i_oracle_para.Value=System.Convert.DBNull;
					}
					else{
						i_oracle_para.Value =i_dr_value[i_obj_property.getName()];
					}
					break;
				case "System.DateTime":
					if(i_dr_value.IsNull(i_obj_property.getName())){
						i_oracle_para.Value=System.Convert.DBNull;
					}
					else if(Convert.ToDateTime(i_dr_value[i_obj_property.getName()])==Convert.ToDateTime("01/01/1900")){
							i_oracle_para.Value=System.Convert.DBNull;
					}
					else{
						i_oracle_para.Value=i_dr_value[i_obj_property.getName()];	
					}
					break;
				default:
					i_oracle_para.Value=i_dr_value[i_obj_property.getName()];
					break;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		protected string get_client_side_insert_command_text(){
			string v_str_command_text = "begin ";
			v_str_command_text += m_TableName + "_P." + "insert_" + m_TableName ;
			v_str_command_text += "( op_id => :op_id ";
			
			// các tham số khác
			DataRow v_Row;
			v_Row=m_ds.Tables[m_TableName].Rows[0];
			foreach (DataColumn v_Column in m_ds.Tables[m_TableName].Columns){
				if(v_Column.ColumnName!="ID"){
					v_str_command_text += ", ip_" + v_Column.ColumnName + " => "  + ":ip_" + v_Column.ColumnName;
				}
			}
			v_str_command_text += "); ";
			v_str_command_text += "  end;";
			return v_str_command_text;
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="i_Cmd"></param>
		protected void setParaCollection4CmdForInsert(OracleCommand i_Cmd){
			// tham số op_id cho trường hợp insert
			i_Cmd.Parameters.Add(DBParamUtil.getDecimalOutputParam("op_id",0));
			// các tham số khác
			DataRow v_Row;
			OracleParameter v_Para;
			v_Row=m_ds.Tables[m_TableName].Rows[0];
			CProperty v_Property=new CProperty();
			foreach (DataColumn v_Column in m_ds.Tables[m_TableName].Columns){
				if(v_Column.ColumnName!="ID"){
					v_Property.SetValues(v_Column.DataType,v_Column.ColumnName);
					v_Para=v_Property.CreateSQLValuePara();
					set_value_4_para(v_Para,v_Property,v_Row);
					v_Para.ParameterName = "ip_"  + v_Para.ParameterName;
					i_Cmd.Parameters.Add(v_Para);
				}
			}
			
		}



		//Set parameter for Update and delete
		protected void setParaCollection4CmdForUD(OracleCommand i_Cmd){
			DataRow v_Row = m_ds.Tables[m_TableName].Rows[0];
			OracleParameter v_Para;
			CProperty v_Property = new CProperty();
			foreach (DataColumn v_Column in m_ds.Tables[m_TableName].Columns){
					v_Property.SetValues(v_Column.DataType,v_Column.ColumnName);
					//tao cac tham so truyen vao cho Stored Procedure
					v_Para=v_Property.CreateSQLValuePara();
					set_value_4_para(v_Para,v_Property,v_Row);
					v_Para.ParameterName = "ip_"  + v_Para.ParameterName;
					i_Cmd.Parameters.Add(v_Para);
			}
		}

		
	}
}
